package com.ysh.service;


import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.Query;

import org.apache.log4j.Logger;


import com.ysh.gsm.Call;

import com.ysh.gsm.Callleg;
import com.ysh.gsm.CdrMR;
import com.ysh.gsm.TimeDimension;
import java.sql.Timestamp;
import java.util.Date;
import java.util.List;
public class DBTestService {
	
	private static final String PERSISTENCE_UNIT = "default";
	private static Logger logger = Logger.getLogger(DBTestService.class);
	
	private  TimeDimension mockTimeDimension(){		
		TimeDimension td =new TimeDimension(2012,2,10,16,5,0,100,2012,20);				
		return td;
	}
	private Call mockCalls(){		
//		TimeDimension td =new TimeDimension(2012,2,10,16,5,0,100,2012,20);
//		td.setTimeKey(1);
//		Date now= new Date();
//		byte ct=0;
//		byte rx=1;
//		byte rxq=1;
//		byte fl=1;
//		byte ha=5;
//		short ext=989;
//		Call call=new Call(
//				5000L,ct,1380000000,1234567,5432176,(now.(int)getTime()/1000),
//				ext,rxq,400,"just fail",
//			fl,22,ha,rx,((int)getTime()/1000+5000),ext,99,td	
//				);
		return null;
	}
	private Callleg mockCallLeg(){
		
		EntityManagerFactory emf = Persistence
				.createEntityManagerFactory(PERSISTENCE_UNIT);

		EntityManager em = emf.createEntityManager();
		Query qt = em.createNamedQuery("timeDimensions.byId");
		qt.setParameter("timeKey", 1);
		TimeDimension td = (TimeDimension) qt.getSingleResult();
		Query qc = em.createNamedQuery("calls.byId");
		qc.setParameter("callKey", 2L);
		Call call = (Call) qc.getSingleResult();
		
		Date now= new Date();
		short ext=989;
//		Callleg leg= new Callleg(cpk,50,30,20,10,40,new Timestamp(now.getTime()),ext,
//				new Timestamp(now.getTime()+5000),ext,4,3,2,1,0,0,9000L,"file_prefix",500,1000,td);
		
		return null;
	}
	private  void timeDimensionTest( TimeDimension td) throws Exception{
		
		EntityManagerFactory emf = Persistence
				.createEntityManagerFactory(PERSISTENCE_UNIT);

		EntityManager em = emf.createEntityManager();
		if (td.getTimeKey() == null
				||td.getTimeKey() == 0) {
			
			td.setTimeKey(null);
			
		} else {
		}
		
		EntityTransaction tx = em.getTransaction();
		tx.begin();
		try {
			em.merge(td);
			tx.commit();
		} catch (Exception e) {
			logger.error("** Error: " + e.getMessage());
			tx.rollback();
			throw new Exception(e.getMessage());
		} finally {
			logger.info("** Closing Entity Manager.");
			em.close();
		}
		
	}
	
	public void deleteTimeDimension() {
		logger.debug("** deleteTimeDimension called...");

		EntityManagerFactory emf = Persistence
				.createEntityManagerFactory(PERSISTENCE_UNIT);
		EntityManager em = emf.createEntityManager();
		Query q = em.createNamedQuery("timeDimensions.dt");
		q.setParameter(1, 2012);
		q.setParameter(2, 1);
		q.setParameter(3, 13);
		q.setParameter(4, 15);
		TimeDimension timeDimension = (TimeDimension) q.getSingleResult();
		

		if (timeDimension != null) {
			logger.info("*KEY*-- "+timeDimension.getTimeKey());
			Query qcall = em.createNamedQuery("calls.timeKey");
			qcall.setParameter("timeDimension", timeDimension);
			
			List<Call> cl = (List<Call>) qcall.getResultList();
			
			for(Call c:cl){
				
				logger.info("*Call-KEY*-- "+c.getCallKey());
			}
//			EntityTransaction tx = em.getTransaction();
//			tx.begin();
//			try {
//				em.remove(timeDimension);
//				tx.commit();
//			} catch (Exception e) {
//				logger.error("** Error: " + e.getMessage());
//				tx.rollback();
//			} finally {
//				logger.info("** Closing Entity Manager.");
//				em.close();
//			}
		}
	}
	public void deleteCall(Long call) {
		logger.debug("** deleteTimeDimension called...");

		EntityManagerFactory emf = Persistence
				.createEntityManagerFactory(PERSISTENCE_UNIT);
		EntityManager em = emf.createEntityManager();
		Query q = em.createNamedQuery("calls.byId");
		q.setParameter("callKey", call);
		Call cl = (Call) q.getSingleResult();

		if (cl != null) {
			EntityTransaction tx = em.getTransaction();
			tx.begin();
			try {
				em.remove(cl);
				tx.commit();
			} catch (Exception e) {
				logger.error("** Error: " + e.getMessage());
				tx.rollback();
			} finally {
				logger.info("** Closing Entity Manager.");
				em.close();
			}
		}
	}
	private void addCall(Call call) throws Exception{
		EntityManagerFactory emf = Persistence
				.createEntityManagerFactory(PERSISTENCE_UNIT);

		EntityManager em = emf.createEntityManager();
		if (call.getCallKey() == null
				||call.getCallKey() == 0) {
			
			call.setCallKey(null);
			
		} else {
		}
		
		EntityTransaction tx = em.getTransaction();
		tx.begin();
		try {
			em.merge(call);
			tx.commit();
		} catch (Exception e) {
			logger.error("** Error: " + e.getMessage());
			tx.rollback();
			throw new Exception(e.getMessage());
		} finally {
			logger.info("** Closing Entity Manager.");
			em.close();
		}
		
	}
	private void addCallLeg() throws Exception{
		EntityManagerFactory emf = Persistence
				.createEntityManagerFactory(PERSISTENCE_UNIT);

		EntityManager em = emf.createEntityManager();
		Query qt = em.createNamedQuery("timeDimensions.byId");
		qt.setParameter("timeKey", 1);
		TimeDimension td = (TimeDimension) qt.getSingleResult();
		Query qc = em.createNamedQuery("calls.byId");
		qc.setParameter("callKey", 1L);
		Call call = (Call) qc.getSingleResult();
		
		Date now= new Date();
		short ext=955;
//		Callleg leg= new Callleg(cpk,50,30,20,10,40,new Timestamp(now.getTime()),ext,
//				new Timestamp(now.getTime()+5000),ext,4,3,2,1,0,0,5000L,"file_prefix",500,1000,td);
//		leg.setCallLegKey(1);
		EntityTransaction tx = em.getTransaction();
		tx.begin();
		try {
		//	em.merge(leg);
			tx.commit();
		} catch (Exception e) {
			logger.error("** Error: " + e.getMessage());
			tx.rollback();
			throw new Exception(e.getMessage());
		} finally {
			logger.info("** Closing Entity Manager.");
			em.close();
		}
		
	}
	public List<CdrMR> getAllCalls(){
		EntityManagerFactory entityManagerFactory = Persistence
				.createEntityManagerFactory(PERSISTENCE_UNIT);

		EntityManager em = entityManagerFactory.createEntityManager();

//		String sql="select c from Call c left join c.timeDimension " +
//				"where c.timeDimension.hour between 11 and 15 and " +
//				"c.timeDimension.month between 2 and 2 and c.timeDimension.day between 21 and 21 and c.timeDimension.year between 2012 and 2012 and " +
//				"c.startTime between '2012-02-21 11:40' and '2012-02-21 13:50'";
//		
		String sq2="Select c from Call c left join c.timeDimension " +
				"where c.timeDimension.hour between 11 and 12 and " +
				"c.timeDimension.month between 3 and 3 and " +
				"c.timeDimension.day between 19 and 19 and " +
				"c.timeDimension.year between 2012 and 2012 " 
				;
//		String sq3="Select cm from CdrMr sm left join cm.callLeg cal where cal.call=:ccall";
		

		Query q = em.createQuery(sq2);
		List<Call> calls = q.getResultList();
		if (calls != null)
			logger.debug("** Found " + calls.size() + " records:");
		em.close();
//		return calls;
//		
//		Query qc = em.createNamedQuery("calls.byId");
//		qc.setParameter("callKey", 2L);
//		Call call = (Call) qc.getSingleResult();
//		Query q = em.createNamedQuery("cmr.findByCall");
//		q.setParameter("call", call);
//		List<CdrMR> calls = q.getResultList();
//		if (calls != null)
//			logger.debug("** Found cdrmr" + calls.size() + " records:");
		em.close();
		return null;
		
	}
	public static void main(String ss[]){
		DBTestService dts=new DBTestService();
		
		try {
			//dts.getAllCalls();
			//dts.getAllCalls();
			//dts.deleteCall(1L);
			//dts.addCall(dts.mockCalls());
			//dts.addCallLeg();
			dts.deleteTimeDimension();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

}
